<?php

class reports_model extends CI_Model 
{
    function __construct()
    {
        // Call the Model constructor
        parent::__construct();
    }

	function getCallNumberRange( $location_id, $prefix, $low_call_number, $high_call_number )
	{
		$this->db->from('tbl_volume');
		$this->db->where('location_id', $location_id);
		if ( !$low_call_number && !$high_call_number )
			$this->db->like( 'call_number = "' . $prefix . '"' );
		
		else
			$this->db->where('call_number BETWEEN "' . $prefix . $low_call_number . '" AND "' . $prefix . $high_call_number . '"' );

		$this->db->order_by('call_number', 'asc');
		return $this->db->get();
	}
	
	function getVolumesByBranch( $location_id )
	{
		$this->db->from('tbl_volume');
		$this->db->where('location_id', $location_id);
		//$this->db->order_by('access_id', 'asc');
		//$this->db->limit(1000);
		return $this->db->get();
	}
	
	// Get overdue loans
	function getOverdueLoans( $location_id, $due_date, $order_by = 'due_date' )
	{	
		$this->db->select('tbl_subscriber.subscriber_id, tbl_subscriber.last_name, tbl_subscriber.first_name, tbl_subscriber.initials, tbl_subscriber.title AS subscriber_title, tbl_subscriber.address1, tbl_subscriber.address2, tbl_subscriber.suburb, tbl_subscriber.post_code, tbl_subscriber.subscriber_status');
		$this->db->select('tbl_loan.access_id, tbl_loan.subscriber_id, tbl_loan.due_date');
		$this->db->select('tbl_volume.access_id, tbl_volume.title, tbl_volume.call_number');
		$this->db->from('tbl_subscriber');
		$this->db->join('tbl_loan', 'tbl_loan.subscriber_id = tbl_subscriber.subscriber_id');
		$this->db->join('tbl_volume', 'tbl_volume.access_id = tbl_loan.access_id');
		if ( $location_id != "All" )
			$this->db->where('tbl_volume.location_id = ', $location_id);
		$this->db->where('tbl_loan.due_date < "' . $due_date . '"');
		$this->db->where('(tbl_subscriber.subscriber_status = "I" OR tbl_subscriber.subscriber_status = "A")');
		$this->db->order_by('tbl_subscriber.subscriber_id', 'asc');
		$this->db->group_by('tbl_loan.access_id');
		return $this->db->get();
	}
	
	// Get overdue loans
	function getSubscribersInArrears( $as_of_date )
	{
		$this->db->from('tbl_subscriber_paid');
		$this->db->join('tbl_subscriber', 'tbl_subscriber_paid.subscriber_id = tbl_subscriber.subscriber_id');
		$this->db->where('expiry_date = (SELECT MAX(expiry_date) FROM tbl_subscriber_paid AS f WHERE f.subscriber_id = tbl_subscriber_paid.subscriber_id)');
		$this->db->where('expiry_date < ', $as_of_date);
		$this->db->where('subscriber_status = "A"');
        return $this->db->get();
	}
	
	// Get overdue loans & arrears
	function getSubscribersInArrearsWithOverdueLoans( $as_of_date )
	{
		$this->db->select('tbl_subscriber.subscriber_id, tbl_subscriber.last_name, tbl_subscriber.first_name, tbl_subscriber.initials, tbl_subscriber.title AS subscriber_title, tbl_subscriber.address1, tbl_subscriber.address2, tbl_subscriber.suburb, tbl_subscriber.post_code, tbl_subscriber.subscriber_status');
		$this->db->select('tbl_subscriber_paid.subscriber_id, tbl_subscriber_paid.expiry_date');
		$this->db->select('tbl_loan.access_id, tbl_loan.subscriber_id, tbl_loan.due_date');
		$this->db->select('tbl_volume.access_id, tbl_volume.title, tbl_volume.call_number');
		$this->db->from('tbl_subscriber_paid');
		$this->db->join('tbl_subscriber', 'tbl_subscriber_paid.subscriber_id = tbl_subscriber.subscriber_id');
		$this->db->join('tbl_loan', 'tbl_loan.subscriber_id = tbl_subscriber.subscriber_id');
		$this->db->join('tbl_volume', 'tbl_volume.access_id = tbl_loan.access_id');
		$this->db->where('expiry_date = (SELECT MAX(expiry_date) FROM tbl_subscriber_paid AS f WHERE f.subscriber_id = tbl_subscriber_paid.subscriber_id)');
		$this->db->where('expiry_date < ', $as_of_date);
		$this->db->where('tbl_loan.due_date < ', $as_of_date);
		$this->db->where('(subscriber_status = "A" OR subscriber_status = "I")');
		$this->db->order_by('tbl_subscriber.subscriber_id', 'asc');
		//$this->db->group_by('tbl_loan.access_id');
		return $this->db->get();
	}
	
	// Get Financial Subscribers
	function getFinancialSubscribers( $as_of_date )
	{
		$this->db->from('tbl_subscriber_paid');
		$this->db->join('tbl_subscriber', 'tbl_subscriber_paid.subscriber_id = tbl_subscriber.subscriber_id');
		$this->db->where('expiry_date = (SELECT MAX(expiry_date) FROM tbl_subscriber_paid AS f WHERE f.subscriber_id = tbl_subscriber_paid.subscriber_id)');
		$this->db->where('expiry_date > ', $as_of_date);
		$this->db->where('(subscriber_status != "A" OR subscriber_status != "D")');
		$this->db->order_by('tbl_subscriber_paid.expiry_date', 'asc');
		return $this->db->get();
	}
	
	// Get Mailing Labels
	function getActiveAndHonorarySubscribers( )
	{
		$this->db->select('tbl_subscriber.subscriber_id, tbl_subscriber.last_name, tbl_subscriber.first_name, tbl_subscriber.subscriber_status');
		$this->db->from('tbl_subscriber');
		$this->db->where( 'tbl_subscriber.subscriber_status = "A" OR tbl_subscriber.subscriber_status = "H"' );
		return $this->db->get();
	}
	
	function inactivateSubscribers( $as_of_date )
	{
		// Get list of subscribers who have not paid since $as_of_date
		$this->db->from('tbl_subscriber_paid');
		$this->db->join('tbl_subscriber', 'tbl_subscriber_paid.subscriber_id = tbl_subscriber.subscriber_id');
		$this->db->where('expiry_date = (SELECT MAX(expiry_date) FROM tbl_subscriber_paid AS f WHERE f.subscriber_id = tbl_subscriber_paid.subscriber_id)');
		$this->db->where('expiry_date < ', $as_of_date);
		$this->db->where('subscriber_status = "A"');
		$subscribers_to_inactivate = $this->db->get();
		
		// Inactivate each subscriber
		foreach ($subscribers_to_inactivate->result() as $subscriber)
		{
			$this->db->where('subscriber_id', $subscriber->subscriber_id);
			$this->db->update('tbl_subscriber', array('subscriber_status'=>'I'));
		}
		
		return $subscribers_to_inactivate->num_rows();
	}
}

?>